/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Metodos;

import Atributos.Disciplina;
import Atributos.Professor;
import Atributos.ProfessorDisciplina;
import BD.CriaConexao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Andre
 */
public class ProfDiscMet {
    private Connection conexao;
    public ProfDiscMet(){
        try {
            this.conexao=(Connection) CriaConexao.getConexao();
        } catch (SQLException ex) {
            Logger.getLogger(ProfDiscMet.class.getName()).log(Level.SEVERE, null, ex);
        }
        
    }

    public void Adiciona(int id_prof, Disciplina d) {
        String sql = "insert into prof_disc (id_prof_pd,id_disc_pd)"+" values (?,?)";
            
            try{
            try (PreparedStatement consulta = (PreparedStatement) conexao.prepareStatement(sql)) {
                consulta.setInt(1, id_prof);
                consulta.setInt(2, d.getId_disc());
                consulta.execute();
            }
            
        }catch(SQLException ex){
            Logger.getLogger(ProfDiscMet.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public List<Disciplina> BuscaDiscProf(int id) {
         String sql = "select disciplina.nome_disc, prof_disc.id_disc_pd,disciplina.eixo from prof_disc,disciplina where prof_disc.id_disc_pd=disciplina.id_disc and prof_disc.id_prof_pd = ?";
        
            List<Disciplina> listadisc = new ArrayList<>();
         try {
            try (PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql)) {
                stmt.setInt(1, id);
                try (ResultSet rs = stmt.executeQuery()) {
                    while (rs.next()) {
                        Disciplina pd = new Disciplina();
                        
                        pd.setNome(rs.getString("nome_disc"));
                        pd.setId_disc(rs.getInt("id_disc_pd"));
                        pd.setEixo(rs.getString("eixo"));
                        listadisc.add(pd);
                        
                    }
                }
            }
           
        } catch (SQLException ex) {
            Logger.getLogger(ProfessorSerieTurmaMet.class.getName()).log(Level.SEVERE, null, ex);
        }
          return listadisc;  
    }

    public List<Disciplina> BuscaNomeDisciEixo(int id) {
        String sql = "select distinct disciplina.eixo from prof_disc,disciplina where prof_disc.id_disc_pd=disciplina.id_disc and prof_disc.id_prof_pd = ?";
        
            List<Disciplina> listadisc = new ArrayList<>();
         try {
            try (PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql)) {
                stmt.setInt(1, id);
                try (ResultSet rs = stmt.executeQuery()) {
                    while (rs.next()) {
                        Disciplina pd = new Disciplina();
                        
                        
                        pd.setEixo(rs.getString("eixo"));
                        listadisc.add(pd);
                        
                    }
                }
            }
           
        } catch (SQLException ex) {
            Logger.getLogger(ProfessorSerieTurmaMet.class.getName()).log(Level.SEVERE, null, ex);
        }
          return listadisc;  
    }

    public void Deletar(int a) {
         String sql_h = "delete from prof_disc where id_prof_pd=?";
        try {
            try (java.sql.PreparedStatement consulta_h = conexao.prepareStatement(sql_h)) {
                consulta_h.setInt(1, a);
            
            consulta_h.execute();
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProfDiscMet.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        
    }

    public List<Disciplina> BuscarDiasciplinasdoProfessor(Professor p) throws SQLException {
        String sql="select disciplina.id_disc,disciplina.nome_disc from disciplina,professor,prof_disc where professor.id_prof=prof_disc.id_prof_pd and prof_disc.id_disc_pd=disciplina.id_disc and professor.nome_prof like ?";
     List<Disciplina> minhalista= new ArrayList<>();
        PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql);
        stmt.setString(1, p.getNome());
        
                ResultSet rs = stmt.executeQuery();
             while (rs.next()) {
                Disciplina a1 = new Disciplina();
                a1.setId_disc(rs.getInt("id_disc"));
                a1.setNome(rs.getString("nome_disc"));
                            
                minhalista.add(a1);
            }
        
        return minhalista;
        
        
        
        
        
        
        
                }
    public int Buscar_DisciplinaID(String nome){
         String sql = "select * from disciplina where nome_disc like ?";
            int id=0;
         try {
            try (PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql); ResultSet rs = stmt.executeQuery()) {
                
                while (rs.next()){
                    Disciplina c = new Disciplina();
                    
                    c.setId_disc(rs.getInt("id_disc"));
                    id=c.getId_disc();
                    
                }
            }
           
        } catch (SQLException ex) {
            Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
        }
          return id;
    }
     public void adiciona(ProfessorDisciplina pd) throws SQLException{

    
        String sql="insert into prof_disc(id_prof_pd,id_disc_pd)"+"values(?,?)";
        try (PreparedStatement consulta = (PreparedStatement) conexao.prepareStatement(sql)) {
            consulta.setInt(1, pd.getId_prof());
            consulta.setInt(2, pd.getId_disc());
            consulta.execute();
        }
       
    
}

    public List BuscarDisciplinasProf(String a,String b,String c,String d,String e,String f,String g,String h) throws SQLException {
        List minhalista= new ArrayList<>();
        if(!"".equals(a)){
            
        String sql="select DISTINCT professor.nome_prof from professor,serie,descricao,professor_serie_turma where serie.nome_serie='3' and descricao.descricao_desc =? and serie.id_turma_s=descricao.id_desc and professor_serie_turma.id_prof_pst=professor.id_prof and professor_serie_turma.id_serie_turma_pst=serie.id_serie";
        PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql);
        stmt.setString(1, a);
        ResultSet rs = stmt.executeQuery();
             while (rs.next()) {
                
                String nome=rs.getString("nome_prof");
                
                int asd=0;
                for(int as=0;minhalista.size()>as;as++){
                if( nome.equals(minhalista.get(as).toString())){
                   asd=1; 
                }  
                }
                if(asd==0){
                minhalista.add(nome);
               
                }
            }
             
        }
        if(!"".equals(b)){
            
        String sql="select DISTINCT professor.nome_prof from professor,serie,descricao,professor_serie_turma where serie.nome_serie='3' and descricao.descricao_desc =? and serie.id_turma_s=descricao.id_desc and professor_serie_turma.id_prof_pst=professor.id_prof and professor_serie_turma.id_serie_turma_pst=serie.id_serie";
        PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql);
        stmt.setString(1, b);
        ResultSet rs = stmt.executeQuery();
             while (rs.next()) {
                
                String nome=rs.getString("nome_prof");
                int asd=0;
                for(int as=0;minhalista.size()>as;as++){
                if( nome.equals(minhalista.get(as).toString())){
                   asd=1; 
                }  
                }
                if(asd==0){
                minhalista.add(nome);
                }
            }
        }
        if(!"".equals(c)){
            
        String sql="select DISTINCT professor.nome_prof from professor,serie,descricao,professor_serie_turma where serie.nome_serie='3' and descricao.descricao_desc =? and serie.id_turma_s=descricao.id_desc and professor_serie_turma.id_prof_pst=professor.id_prof and professor_serie_turma.id_serie_turma_pst=serie.id_serie";
        PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql);
        stmt.setString(1, c);
        ResultSet rs = stmt.executeQuery();
             while (rs.next()) {
                
                String nome=rs.getString("nome_prof");
                int asd=0;
                for(int as=0;minhalista.size()>as;as++){
                if(nome.equals(minhalista.get(as).toString())){
                   asd=1; 
                }  
                }
                if(asd==0){
                minhalista.add(nome);
                }
            }
        }
        if(!"".equals(d)){
            
        String sql="select DISTINCT professor.nome_prof from professor,serie,descricao,professor_serie_turma where serie.nome_serie='3' and descricao.descricao_desc =? and serie.id_turma_s=descricao.id_desc and professor_serie_turma.id_prof_pst=professor.id_prof and professor_serie_turma.id_serie_turma_pst=serie.id_serie";
        PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql);
        stmt.setString(1, d);
        ResultSet rs = stmt.executeQuery();
             while (rs.next()) {
                
                String nome=rs.getString("nome_prof");
                int asd=0;
                for(int as=0;minhalista.size()>as;as++){
                if(nome.equals(minhalista.get(as).toString())){
                   asd=1; 
                }  
                }
                if(asd==0){
                minhalista.add(nome);
                }
            }
        }
        if(!"".equals(e)){
            
        String sql="select DISTINCT professor.nome_prof from professor,serie,descricao,professor_serie_turma where serie.nome_serie='3' and descricao.descricao_desc =? and serie.id_turma_s=descricao.id_desc and professor_serie_turma.id_prof_pst=professor.id_prof and professor_serie_turma.id_serie_turma_pst=serie.id_serie";
        PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql);
        stmt.setString(1, e);
        ResultSet rs = stmt.executeQuery();
             while (rs.next()) {
                
                String nome=rs.getString("nome_prof");
                int asd=0;
                for(int as=0;minhalista.size()>as;as++){
                if(nome.equals(minhalista.get(as).toString())){
                   asd=1; 
                }  
                }
                if(asd==0){
                minhalista.add(nome);
                }
            }
        }
        if(!"".equals(f)){
            
        String sql="select DISTINCT professor.nome_prof from professor,serie,descricao,professor_serie_turma where serie.nome_serie='3' and descricao.descricao_desc =? and serie.id_turma_s=descricao.id_desc and professor_serie_turma.id_prof_pst=professor.id_prof and professor_serie_turma.id_serie_turma_pst=serie.id_serie";
        PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql);
        stmt.setString(1, f);
        ResultSet rs = stmt.executeQuery();
             while (rs.next()) {
                
                String nome=rs.getString("nome_prof");
                int asd=0;
                for(int as=0;minhalista.size()>as;as++){
                if(nome.equals(minhalista.get(as).toString())){
                   asd=1; 
                }  
                }
                if(asd==0){
                minhalista.add(nome);
                }
            }
        }
        if(!"".equals(g)){
            
        String sql="select DISTINCT professor.nome_prof from professor,serie,descricao,professor_serie_turma where serie.nome_serie='3' and descricao.descricao_desc =? and serie.id_turma_s=descricao.id_desc and professor_serie_turma.id_prof_pst=professor.id_prof and professor_serie_turma.id_serie_turma_pst=serie.id_serie";
        PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql);
        stmt.setString(1, g);
        ResultSet rs = stmt.executeQuery();
             while (rs.next()) {
                
                String nome=rs.getString("nome_prof");
                int asd=0;
                for(int as=0;minhalista.size()>as;as++){
                if(nome.equals(minhalista.get(as).toString())){
                   asd=1; 
                }  
                }
                if(asd==0){
                minhalista.add(nome);
                }
            }
        }
        if(!"".equals(h)){
            
        String sql="select DISTINCT professor.nome_prof from professor,serie,descricao,professor_serie_turma where serie.nome_serie='3' and descricao.descricao_desc =? and serie.id_turma_s=descricao.id_desc and professor_serie_turma.id_prof_pst=professor.id_prof and professor_serie_turma.id_serie_turma_pst=serie.id_serie";
        PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql);
        stmt.setString(1, h);
        ResultSet rs = stmt.executeQuery();
             while (rs.next()) {
                
                String nome=rs.getString("nome_prof");
                int asd=0;
                for(int as=0;minhalista.size()>as;as++){
                if(nome.equals(minhalista.get(as).toString())){
                   asd=1; 
                }  
                }
                if(asd==0){
                minhalista.add(nome);
                }
            }
        }
        return minhalista;
//        String sql="select DISTINCT professor.nome_prof from professor,serie,descricao where serie.nome_serie=3 and descricao.descricao_desc =? or  descricao.descricao_desc =? or descricao.descricao_desc =? or descricao.descricao_desc =? or descricao.descricao_desc =? or descricao.descricao_desc =? or descricao.descricao_desc =? or descricao.descricao_desc =? and serie.id_turma_s=descricao.id_desc";
//     List minhalista= new ArrayList<>();
//        PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql);
//        stmt.setString(1, a);
//        stmt.setString(2, b);
//        stmt.setString(3, c);
//        stmt.setString(4, d);
//        stmt.setString(5, e);
//        stmt.setString(6, f);
//        stmt.setString(7, g);
//        stmt.setString(8, h);
//        
//                ResultSet rs = stmt.executeQuery();
//             while (rs.next()) {
//                
//                String nome=rs.getString("nome_prof");
//                
//                            
//                minhalista.add(nome);
//            }
//        
//        return minhalista;
        
    }

   

    
}
